﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Linq.Mapping;
using Microsoft.Data.Sqlite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using KorbeRTR.Utility;


namespace KorbeRTR.Models
{
    /// <summary>
    /// 故障代码
    /// </summary>
    [Table(Name = "AlarmCodeTable")]
    public class AlarmCode
    {
        [Column]
        public Int64 Id { get; set; }

        /// <summary>
        /// 单元号
        /// </summary>
        [Column]
        public Int64 Addr { get; set; }

        /// <summary>
        /// 位地址
        /// </summary>
        [Column]
        public Int64 SubAddr { get; set; }

        /// <summary>
        /// 绝对地址
        /// </summary>
        public string AbsAddr => string.Format("{0}.{1}", Addr, SubAddr);

        /// <summary>
        /// 故障代码
        /// </summary>
        [Column]
        public Int64 Code { get; set; }

        /// <summary>
        /// 故障内容
        /// </summary>
        [Column]
        public string Content { get; set; }

        /// <summary>
        /// 故障类型
        /// </summary>
        [Column]
        public string AlarmType { get; set; }

        /// <summary>
        /// 是否处理
        /// </summary>
        [Column]
        public bool Handle { get; set; }

        /// <summary>
        /// 发生槽号
        /// </summary>
        [Column]
        public Int64 Slot { get; set; }

        /// <summary>
        /// 软删除标志
        /// </summary>
        [Column]
        public bool IsDeleted { get; set; }

        /// <summary>
        /// 添加故障代码
        /// </summary>
        /// <returns></returns>
        public static bool Add(AlarmCode code)
        {
            int rows = AlarmRecordDBHelper.ExecuteNonQuery(
                @"INSERT INTO AlarmCodeTable(Addr,SubAddr,Code,Content,AlarmType,Handle,Slot)
                                      VALUES(@Addr,@SubAddr,@Code,@Content,@AlarmType,@Handle,@Slot)",
                new SqliteParameter("@Addr", code.Addr),
                new SqliteParameter("@SubAddr", code.SubAddr),
                new SqliteParameter("@Code", code.Code),
                new SqliteParameter("@Content", code.Content),
                new SqliteParameter("@AlarmType", code.AlarmType),
                new SqliteParameter("@Handle", code.Handle),
                new SqliteParameter("@Slot", code.Slot)
                );
            return rows > 0;
        }

        /// <summary>
        /// 获取所有报警类型
        /// </summary>
        public static List<string> ListAllAlarmType()
        {
            var dt = AlarmCodeDBHelper.ExecuteTable("SELECT DISTINCT(AlarmType) FROM AlarmCodeTable");

            var list = new List<string>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add((string)dr[0]);
            }

            return list;
        }

        /// <summary>
        /// 列出全部故障代码
        /// </summary>
        /// <returns></returns>
        public static List<AlarmCode> ListAll()
        {
            var dt = AlarmCodeDBHelper.ExecuteTable("SELECT * FROM AlarmCodeTable WHERE IsDeleted=FALSE");

            return dt.ToModel<AlarmCode>();
        }

        public static AlarmCode SelectWhereAddrAndSubAddr(int addr, int subaddr)
        {
            var dt = AlarmCodeDBHelper.ExecuteTable("SELECT * FROM AlarmCodeTable WHERE IsDeleted=FALSE AND Addr=@Addr AND SubAddr=@SubAddr",
                new SqliteParameter("@Addr", addr),
                new SqliteParameter("@SubAddr", subaddr));

            var list = dt.ToModel<AlarmCode>();

            return (list.Count > 0) ? list[0] : null;
        }

        /// <summary>
        /// 列出指定故障类型的故障代码
        /// </summary>
        /// <returns></returns>
        public static List<AlarmCode> SelectByAlarmType(string AlarmType)
        {
            var dt = AlarmCodeDBHelper.ExecuteTable("SELECT * FROM AlarmCodeTable WHERE IsDeleted=FALSE AND AlarmType=@AlarmType",
                new SqliteParameter("@AlarmType", AlarmType));

            return dt.ToModel<AlarmCode>();
        }

        public static bool UpdateWhereAddrAndSubAddr(AlarmCode code)
        {
            int rows = AlarmRecordDBHelper.ExecuteNonQuery(
                @"UPDATE AlarmCodeTable SET Content=@Content,AlarmType=@AlarmType,Handle=@Handle,Slot=@Slot WHERE Addr=@Addr AND SubAddr=@SubAddr",
                new SqliteParameter("@Content", code.Content),
                new SqliteParameter("@AlarmType", code.AlarmType),
                new SqliteParameter("@Handle", code.Handle),
                new SqliteParameter("@Slot", code.Slot),
                new SqliteParameter("@Addr", code.Addr),
                new SqliteParameter("@SubAddr", code.SubAddr)
                );
            return rows > 0;
        }
    }

    /// <summary>
    /// 故障记录
    /// </summary>
    [Table(Name = "AlarmRecords")]
    public class AlarmRecordView
    {
        [Column]
        public Int64 Id { get; set; }

        /// <summary>
        /// 软删除标志
        /// </summary>
        [Column]
        public bool IsDeleted { get; set; }

        /// <summary>
        /// 开始时间
        /// </summary>
        [Column]
        public DateTime StartTime { get; set; }

        /// <summary>
        /// 结束时间
        /// </summary>
        [Column]
        public DateTime EndTime { get; set; }

        /// <summary>
        /// 持续时间
        /// </summary>
        public TimeSpan Duration => EndTime - StartTime;

        /// <summary>
        /// 报警ID
        /// </summary>
        [Column]
        public Int64 AlarmCodeId { get; set; }

        /// <summary>
        /// 报警信息
        /// </summary>
        public AlarmCode AlarmCode;

        public string Content => (AlarmCode!=null) ?
            string.Format("{0}({1}.{2})", AlarmCode.Content, AlarmCode.Addr, AlarmCode.SubAddr) : "";

        public string AlarmType => (AlarmCode != null) ? AlarmCode.AlarmType : "";

        /// <summary>
        /// 删除n个月前的数据
        /// </summary>
        public static bool DeleteBeforeMonth(DateTime time, int n)
        {
            time = time.AddMonths(-n);
            time = new DateTime(time.Year, time.Month, 1, 0, 0, 0);

            int rows = AlarmRecordDBHelper.ExecuteNonQuery("DELETE FROM AlarmRecords WHERE StartTime < @Time",
                new SqliteParameter("@Time", time.ToSqlTime())
                );
            return rows > 0;
        }

        /// <summary>
        /// 添加报警记录
        /// </summary>
        public static bool Add(AlarmRecordView view)
        {
            int rows = AlarmRecordDBHelper.ExecuteNonQuery("INSERT INTO AlarmRecords(StartTime,EndTime,AlarmCodeId) VALUES(@StartTime,@EndTime,@AlarmCodeId)",
                new SqliteParameter("@StartTime", view.StartTime.ToSqlTime()),
                new SqliteParameter("@EndTime", view.EndTime.ToSqlTime()),
                new SqliteParameter("@AlarmCodeId", view.AlarmCode.Id)
                );
            return rows > 0;
        }

        /// <summary>
        /// 添加报警记录
        /// </summary>
        public static bool Add(SqliteTransaction trans,AlarmRecordView view)
        {
            int rows = AlarmRecordDBHelper.ExecuteNonQuery(trans, "INSERT INTO AlarmRecords(StartTime,EndTime,AlarmCodeId) VALUES(@StartTime,@EndTime,@AlarmCodeId)",
                new SqliteParameter("@StartTime", view.StartTime.ToSqlTime()),
                new SqliteParameter("@EndTime", view.EndTime.ToSqlTime()),
                new SqliteParameter("@AlarmCodeId", view.AlarmCode.Id)
                );
            return rows > 0;
        }

        /// <summary>
        /// 列出全部故障记录
        /// </summary>
        /// <returns></returns>
        public static List<AlarmRecordView> ListAll()
        {
            var dt = AlarmRecordDBHelper.ExecuteTable(
                @"SELECT * FROM AlarmRecords WHERE IsDeleted=FALSE");

            var list = dt.ToModel<AlarmRecordView>();

            var dict = new Dictionary<Int64, AlarmCode>();
            {
                var tmp = AlarmCode.ListAll();
                foreach(var iter in tmp)
                {
                    dict[iter.Id] = iter;
                }
            }

            // 匹配报警内容
            foreach(var iter in list)
            {
                iter.AlarmCode = dict.ContainsKey(iter.AlarmCodeId)? dict[iter.AlarmCodeId] : null;
            }

            return list;
        }

        /// <summary>
        /// 查询 t0 到 t1 时间内的记录
        /// </summary>
        /// <param name="t0">起始时间</param>
        /// <param name="t1">终止时间</param>
        /// <returns></returns>
        public static List<AlarmRecordView> Select(DateTime t0, DateTime t1)
        {
            if (t0 > t1)
            {
                AuxLib.Swap(ref t0, ref t1);
            }

            var dt = AlarmRecordDBHelper.ExecuteTable(
                @"SELECT * FROM AlarmRecords WHERE IsDeleted=FALSE AND (StartTime BETWEEN @T0 AND @T1)",
                new SqliteParameter("@T0", t0.ToSqlTime()),
                new SqliteParameter("@T1", t1.ToSqlTime())
                );

            var list = dt.ToModel<AlarmRecordView>();

            var dict = new Dictionary<Int64, AlarmCode>();
            {
                var tmp = AlarmCode.ListAll();
                foreach (var iter in tmp)
                {
                    dict[iter.Id] = iter;
                }
            }

            // 匹配报警内容
            foreach (var iter in list)
            {
                iter.AlarmCode = dict.ContainsKey(iter.AlarmCodeId) ? dict[iter.AlarmCodeId] : null;
            }

            return list;
        }

        /// <summary>
        /// 查询N天数据
        /// </summary>
        /// <param name="start">起始日期</param>
        /// <param name="days">天数</param>
        /// <returns></returns>
        public static List<AlarmRecordView> Select(DateTime aday, int days)
        {
            var t0 = aday.Date;
            var t1 = aday.Date.AddDays(days);
            return Select(t0, t1);
        }

        /// <summary>
        /// 查询某天的数据
        /// </summary>
        /// <param name="day"></param>
        /// <returns></returns>
        public static List<AlarmRecordView> Select(DateTime day)
        {
            return Select(day, 1);
        }
    }
}
